
package org.netEase.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.netEase.dao.commentDao;
import org.netEase.entity.comment;
import org.netEase.entity.shangping;
import org.netEase.entity.yonghu;
import org.netEase.entity.zhanghu;
import org.netEase.util.BaseDao;





public class commentDaoImple extends BaseDao implements commentDao {

	@Override
	public List<comment> getbyDate(String d) {
		List<comment> list = new ArrayList<>();
		String sql = "SELECT c.id,z.namesp,c.com_images,c.Com_date,c.com_content,c.com_praise,sh.Sname,sh.Sjiage,c.hid FROM COMMENT c INNER JOIN zhanghu z ON c.hid=z.hid INNER JOIN shangping sh ON c.sid=sh.sid WHERE c.Com_date LIKE concat(?,'%')";
		ResultSet rs = super.executeQuery(sql, d);
		try {
			while(rs.next()) {
				comment comment = new comment();
				comment.setId(rs.getInt("id"));
				yonghu yonghu = new yonghu();
				yonghu.setNamesp(rs.getString("namesp"));
				comment.setYonghu(yonghu);
				comment.setCom_images(rs.getString("com_images"));
				comment.setCom_content(rs.getString("com_content"));
				comment.setCom_date(rs.getTimestamp("com_date"));
				comment.setCom_praise(rs.getInt("com_praise"));
				comment.setCom_hid(rs.getInt("hid"));
				shangping shangping = new shangping();
				shangping.setShopname(rs.getString("sname"));
				shangping.setSjiage(rs.getDouble("Sjiage"));
				comment.setShangping(shangping);
				
				list.add(comment);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				closeAll(rs.getStatement().getConnection(), rs.getStatement(), rs);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return list;
	}

	@Override
	public List<comment> getbyDate() {
		List<comment> list = new ArrayList<>();
		String sql = "SELECT c.id,z.namesp,c.com_images,c.Com_date,c.com_content,c.com_praise FROM  COMMENT c INNER JOIN zhanghu z ON c.hid=z.hid  ";
		ResultSet rs = super.executeQuery(sql);
		try {
			while(rs.next()) {
				comment comment = new comment();
				comment.setId(rs.getInt("id"));
				comment.setCom_images(rs.getString("com_images"));
				comment.setCom_content(rs.getString("com_content"));
				comment.setCom_date(rs.getTimestamp("com_date"));
				comment.setCom_praise(rs.getInt("com_praise"));
				yonghu yonghu = new yonghu();
				yonghu.setNamesp(rs.getString("namesp"));
				comment.setYonghu(yonghu);
				list.add(comment);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				closeAll(rs.getStatement().getConnection(), rs.getStatement(), rs);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return list;
	}

	@Override
	public List<comment> getbysid(int pageNO,int sizes,int sid) {
		int pages = (pageNO-1)*sizes;
		String sql = "SELECT c.id,z.namesp,c.com_images,c.Com_date,c.com_content,c.com_praise,s.sid,s.Sname,z.toimg,s.Sjiage FROM  COMMENT c INNER JOIN zhanghu z ON c.hid=z.hid INNER JOIN shangping s ON c.sid = s.sid WHERE c.sid = ? ORDER BY c.com_date DESC LIMIT ?,?";
		List<comment> list = new ArrayList<>();
		ResultSet rs = super.executeQuery(sql, sid,pages,sizes);
		try {
			while (rs.next()) {
				comment comment = new comment();
				comment.setId(rs.getInt("id"));
				yonghu yonghu = new yonghu();
				yonghu.setNamesp(rs.getString("namesp"));
				yonghu.setToimg(rs.getString("toimg"));
				comment.setYonghu(yonghu);
				comment.setCom_images(rs.getString("com_images"));
				comment.setCom_content(rs.getString("com_content"));
				comment.setCom_date(rs.getTimestamp("com_date"));
				comment.setCom_praise(rs.getInt("com_praise"));
				shangping shangping = new shangping();
				shangping.setShopname(rs.getString("sname"));
				shangping.setSjiage(rs.getDouble("Sjiage"));
				comment.setShangping(shangping);
				
				list.add(comment);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				closeAll(rs.getStatement().getConnection(), rs.getStatement(), rs);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return list;
	}

	@Override
	public int getTotCount(int sid) {
		String sql = "select count(*) c from COMMENT where sid = ?";
		int tot = 0;
		ResultSet rs = super.executeQuery(sql, sid);
		try {
			if (rs.next()) {
				tot = rs.getInt("c");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				closeAll(rs.getStatement().getConnection(), rs.getStatement(), rs);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return tot;
	}
	/*public static void main(String[] args) {
		commentDao commentDao = new commentDaoImple();
		List<comment> list =commentDao.getbysid(1, 3, 83);
		for (comment comment : list) {
			System.out.println(list);
		}
	}*/

	@Override
	public comment getIds(int id) {
		String sql = "SELECT c.id,c.com_images,c.Com_date,c.com_content,c.com_praise,c.Sid,z.namesp,z.hid FROM  COMMENT c INNER JOIN zhanghu z ON c.hid=z.hid where c.id=?";
		ResultSet rs = super.executeQuery(sql,id);
		comment com = null;
		try {
			while (rs.next()) {
				com=new comment();
				com.setId(rs.getInt("id"));
				/*yonghu yonghu = new yonghu();
				yonghu.setNamesp(rs.getString("namesp"));
				yonghu.setHid(rs.getInt("hid"));*/
				com.setCom_hid(rs.getInt("hid"));
				com.setCom_images(rs.getString("com_images"));
				com.setCom_content(rs.getString("com_content"));
				com.setCom_date(rs.getTimestamp("com_date"));
				com.setCom_praise(rs.getInt("com_praise"));
				com.setCom_hName(rs.getString("namesp"));
				com.setSid(rs.getInt("sid"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				closeAll(rs.getStatement().getConnection(), rs.getStatement(), rs);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return com;
	}
	public int getCount(int hid) {
		List<Object> condition = new ArrayList<>();
		String sql = "select count(1) c from COMMENT where 1=1";
		if(hid != -1) {
			sql+=" and hid = ?";
			condition.add(hid);
		}
		ResultSet rs = super.executeQuery(sql,condition.toArray());
		int total = 0;
		try {
			if (rs.next()) {
				total = rs.getInt("c");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				super.closeAll(rs.getStatement().getConnection(), rs.getStatement(), rs);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return total;
	}

	@Override
	public List<comment> getall(int pageNO, int sizes,int hid) {
		List<comment> list = new ArrayList<>();
		String sql = "SELECT c.hid,z.name, c.id,z.namesp,c.com_images,c.Com_date,c.com_content,c.com_praise FROM  COMMENT c INNER JOIN zhanghu z ON c.hid=z.hid where 1=1";
	List<Object> params=new ArrayList<>();
//	c.hid=?
	int start=(pageNO - 1)*sizes;
	if(hid !=-1){
		sql+=" and z.hid=?";
		params.add(hid);
	}
	sql+=" limit ?,?";
	params.add(start);
	params.add(sizes);
	ResultSet rs=super.executeQuery(sql, params.toArray());
	try {
		while(rs.next()) {
			comment comment = new comment();
			comment.setId(rs.getInt("id"));
			comment.setCom_images(rs.getString("com_images"));
			comment.setCom_content(rs.getString("com_content"));
			comment.setCom_date(rs.getTimestamp("com_date"));
			comment.setCom_praise(rs.getInt("com_praise"));
			
		
		
			
			yonghu yonghu = new yonghu();
			yonghu.setNamesp(rs.getString("namesp"));
			yonghu.setHid(rs.getInt("hid"));
			yonghu.setName(rs.getString("name"));
			comment.setYonghu(yonghu);
			list.add(comment);
		}
	} catch (SQLException e) {
		e.printStackTrace();
	}
	return list;
		
	
	}
	
	@Override
	public int addcomm(comment comment) {
		String sql = "insert into comment(hid,Com_images,Com_Content,Com_Praise,Sid)values(?,?,?,?,?)";
		return super.executeUpdate(sql, comment.getYonghu().getHid(),comment.getCom_images(),comment.getCom_content(),comment.getCom_praise(),comment.getShangping().getSid());
	}

	@Override
	public List<comment> getbysidtwo(int pageNO,int sizes,int sid) {
		int pages = (pageNO-1)*sizes;
		String sql = "SELECT c.id,z.namesp,c.com_images,c.Com_date,c.com_content,c.com_praise,s.sid,s.Sname,z.toimg,s.Sjiage FROM  COMMENT c INNER JOIN zhanghu z ON c.hid=z.hid INNER JOIN shangping s ON c.sid = s.sid WHERE c.sid = ? ORDER BY c.com_date DESC LIMIT ?,?";
		//String sql = "SELECT c.id,z.namesp,c.com_images,c.Com_date,c.com_content,c.com_praise,s.sid,s.Sname,z.toimg,s.Sjiage FROM  COMMENT c INNER JOIN zhanghu z ON c.hid=z.hid INNER JOIN shangping s ON c.sid = s.sid WHERE c.sid = ? ORDER BY c.com_date DESC";
		List<comment> list = new ArrayList<>();
		ResultSet rs = super.executeQuery(sql, sid,pages,sizes);
		//ResultSet rs = super.executeQuery(sql, sid);
		try {
			while (rs.next()) {
				comment comment = new comment();
				comment.setId(rs.getInt("id"));
				yonghu yonghu = new yonghu();
				yonghu.setNamesp(rs.getString("namesp"));
				yonghu.setToimg(rs.getString("toimg"));
				comment.setYonghu(yonghu);
				comment.setCom_images(rs.getString("com_images"));
				comment.setCom_content(rs.getString("com_content"));
				comment.setCom_date(rs.getTimestamp("com_date"));
				comment.setCom_praise(rs.getInt("com_praise"));
				shangping shangping = new shangping();
				shangping.setShopname(rs.getString("sname"));
				shangping.setSjiage(rs.getDouble("Sjiage"));
				comment.setShangping(shangping);
				
				list.add(comment);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				closeAll(rs.getStatement().getConnection(), rs.getStatement(), rs);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return list;
	}

	@Override
	public List<comment> getbydetilas(int pageNO, int sizes, comment comment2) {
		int pages = (pageNO-1)*sizes;
		List<Object> condition = new ArrayList<>();
		String sql = "SELECT c.id,z.namesp,c.com_images,c.Com_date,c.com_content,c.com_praise,s.sid,s.Sname,z.toimg,s.Sjiage FROM  COMMENT c INNER JOIN zhanghu z ON c.hid=z.hid INNER JOIN shangping s ON c.sid = s.sid WHERE 1=1";
		List<comment> list = new ArrayList<>();
		if (comment2.getCom_images() != "" && comment2.getCom_images() != null) {
			sql+=" and Com_images is not null";
		}
		if (comment2.getCom_praise() > 0) {
			sql+=" and Com_Praise = ?";
			condition.add(comment2.getCom_praise());
		}
		sql+=" and c.sid = ? ORDER BY c.com_date DESC LIMIT ?,?";
		condition.add(comment2.getSid());
		condition.add(pages);
		condition.add(sizes);
		ResultSet rs = super.executeQuery(sql,condition.toArray());
		try {
			while (rs.next()) {
				comment comment = new comment();
				comment.setId(rs.getInt("id"));
				yonghu yonghu = new yonghu();
				yonghu.setNamesp(rs.getString("namesp"));
				yonghu.setToimg(rs.getString("toimg"));
				comment.setYonghu(yonghu);
				comment.setCom_images(rs.getString("com_images"));
				comment.setCom_content(rs.getString("com_content"));
				comment.setCom_date(rs.getTimestamp("com_date"));
				comment.setCom_praise(rs.getInt("com_praise"));
				shangping shangping = new shangping();
				shangping.setShopname(rs.getString("sname"));
				shangping.setSjiage(rs.getDouble("Sjiage"));
				comment.setShangping(shangping);
				
				list.add(comment);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				closeAll(rs.getStatement().getConnection(), rs.getStatement(), rs);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return list;
	}

	@Override
	public int getTotDelCount(comment comment2) {
		int tot = 0;
		List<Object> condition = new ArrayList<>();
		String sql = "select count(*) c from COMMENT where 1=1";
		if (comment2.getCom_images() != "" && comment2.getCom_images() != null) {
			sql+=" and Com_images is not null";
		}
		if (comment2.getCom_praise() > 0) {
			sql+=" and Com_Praise = ?";
			condition.add(comment2.getCom_praise());
		}
		sql+=" and sid = ?";
		condition.add(comment2.getSid());
		ResultSet rs = super.executeQuery(sql, condition.toArray());
		try {
			if (rs.next()) {
				tot = rs.getInt("c");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				closeAll(rs.getStatement().getConnection(), rs.getStatement(), rs);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return tot;
	}
/*	public static void main(String[] args) {
		commentDao commentDao = new commentDaoImple();
		comment comment = new comment();
		comment.setSid(83);
		int a =commentDao.getTotDelCount(comment);
		System.out.println(a);
	}*/

	@Override
	public int CommentCountbySid(int sid) {
		// TODO Auto-generated method stub
		return 0;
	}

	@Override
	public int havimg(int sid) {
		String sql = "select count(*) c from COMMENT where Com_images is not null and sid = ?";
		int tot = 0;
		ResultSet rs = super.executeQuery(sql,sid);
		try {
			if (rs.next()) {
				tot = rs.getInt("c");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				closeAll(rs.getStatement().getConnection(), rs.getStatement(), rs);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return tot;
	}

	@Override
	public int five(int sid) {
		String sql = "select count(*) c from COMMENT where Com_Praise = 5 and sid = ?";
		int tot = 0;
		ResultSet rs = super.executeQuery(sql,sid);
		try {
			if (rs.next()) {
				tot = rs.getInt("c");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				closeAll(rs.getStatement().getConnection(), rs.getStatement(), rs);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return tot;
	}

	@Override
	public int wellSum(int sid) {
		String sql = "SELECT SUM(Com_Praise) cp FROM COMMENT WHERE Com_Praise = 5 and sid = ?";
		int wellSum = 0;
		ResultSet rs = super.executeQuery(sql, sid);
		try {
			if (rs.next()) {
				wellSum = rs.getInt("cp");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				closeAll(rs.getStatement().getConnection(), rs.getStatement(), rs);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return wellSum;
	}

	@Override
	public int TotSum(int sid) {
		String sql = "SELECT SUM(Com_Praise) cp FROM COMMENT WHERE sid = ?";
		int TolSum = 0;
		ResultSet rs = super.executeQuery(sql, sid);
		try {
			if (rs.next()) {
				TolSum = rs.getInt("cp");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				closeAll(rs.getStatement().getConnection(), rs.getStatement(), rs);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return TolSum;
	}
	
	public static void main(String[] args) {
		commentDao commentDao = new commentDaoImple();
		List<comment> list = commentDao.getbyDate("2018-08-04");
		for (comment comment : list) {
			System.out.println(comment);
		}
		
	}

	@Override
	public int dele(int id) {
		String sql = "delete from comment where id = ?";
		return super.executeUpdate(sql, id);
	}
	
}
	
	

